package com.backend.mybatis.generator.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.backend.mybatis.generator.model.DatabaseConfig;
import com.backend.mybatis.generator.model.DbType;
import com.mysql.jdbc.Statement;

/**
 * 
 * @author jason
 */
public class DbUtil {

	private static final Logger _LOG = LoggerFactory.getLogger(DbUtil.class);
	private static final int DB_CONNECTION_TIMEOUTS_SECONDS = 1;

	public static Connection getConnection(DatabaseConfig config) throws ClassNotFoundException, SQLException {
		DbType dbType = DbType.valueOf(config.getDbType());
		Class.forName(dbType.getDriverClass());
		DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
		String url = getConnectionUrlWithSchema(config);
		_LOG.info("getConnection, connection url: {}", url);
		return DriverManager.getConnection(url, config.getUsername(), config.getPassword());
	}

	public static List<String> getTableNames(DatabaseConfig config) throws Exception {
		DbType dbType = DbType.valueOf(config.getDbType());
		Class.forName(dbType.getDriverClass());
		String url = getConnectionUrlWithSchema(config);
		_LOG.info("getTableNames, connection url: {}", url);
		DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
		Connection conn = DriverManager.getConnection(url, config.getUsername(), config.getPassword());
		DatabaseMetaData md = conn.getMetaData();
		ResultSet rs = md.getTables(null, config.getUsername().toUpperCase(), null, null);
		List<String> tables = new ArrayList<>();
		while (rs.next()) {
			tables.add(rs.getString(3));
		}
		return tables;
	}

	public static List getTableColumns(DatabaseConfig dbConfig, String tableName) throws Exception {
		DbType dbType = DbType.valueOf(dbConfig.getDbType());
		Class.forName(dbType.getDriverClass());
		DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
		String url = getConnectionUrlWithSchema(dbConfig);
		_LOG.info("getTableColumns, connection url: {}", url);
		Connection conn = DriverManager.getConnection(url, dbConfig.getUsername(), dbConfig.getPassword());
		DatabaseMetaData md = conn.getMetaData();
		ResultSet rs = md.getColumns(null, null, tableName, null);
		List<String> columns = new ArrayList<>();
		while (rs.next()) {
			columns.add(rs.getMetaData().getSchemaName(3));
		}
		return columns;
	}

	/**
	 * 获得某表中所有字段的注释
	 * 
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static List getColumnCommentByTableName(DatabaseConfig dbConfig, String tableName) throws Exception {
		Map map = new HashMap();
			DbType dbType = DbType.valueOf(dbConfig.getDbType());
			Class.forName(dbType.getDriverClass());
			DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
			String url = getConnectionUrlWithSchema(dbConfig);
			_LOG.info("getTableColumns, connection url: {}", url);
			Connection conn = DriverManager.getConnection(url, dbConfig.getUsername(), dbConfig.getPassword());
			DatabaseMetaData md = conn.getMetaData();
			Statement stmt = (Statement) conn.createStatement(); 
			ResultSet rs = stmt.executeQuery("show full columns from " + tableName); 
			List<String> columns = new ArrayList<>();
			while (rs.next()) {
				columns.add(StringUtils.dbStringToCamelStyle2(rs.getString("Field")) + ":" +  rs.getString("Comment"));
			}
			rs.close();
			return columns;
	}

	public static String getConnectionUrlWithSchema(DatabaseConfig dbConfig) {
		DbType dbType = DbType.valueOf(dbConfig.getDbType());
		String connectionUrl = String.format(dbType.getConnectionUrlPattern(), dbConfig.getHost(), dbConfig.getPort(), dbConfig.getSchema(), dbConfig.getEncoding());
		_LOG.info("getConnectionUrlWithSchema, connection url: {}", connectionUrl);
		return connectionUrl;
	}

}
